﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using NLBS.Entity;
using System.Data;

namespace NLBS.Data
{
    public partial class AccessDataProvider: DbProvider
    {
        #region category

        public override IList<Category> GetCategory()
        {
            DbCommand dbCommand = db.GetSqlStringCommand("SELECT * FROM  blog_Category");
            IList<Category> lists = new List<Category>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Category catrgory = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(catrgory);
                }
            }
            return lists;
        }

        public override Category GetCategoryByID(int catID)
        {
            DbCommand dbCommand = db.GetSqlStringCommand("SELECT * FROM  blog_Category WHERE (cat_id = ?)");
            db.AddInParameter(dbCommand, "catID", DbType.Int32, catID);
            IDataReader dr = db.ExecuteReader(dbCommand);
            Category catrgory = null;
            if (dr.Read())
            {
                catrgory = CommonDataProvider.PopulateCategoryFromIDataReader(dr);
            }
            return catrgory;
        }

        public override int InsertCategory(Category cat)
        {
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();
                try
                {
                    DbCommand dbCommand = db.GetSqlStringCommand(@"INSERT INTO blog_Category 
                    (`cat_name`, `cat_order`, `cat_articleCount`, `cat_hidden`, `cat_locked`) 
                    VALUES (?, ?, ?, ?, ?);
                    ");
                    db.AddInParameter(dbCommand, "catName", DbType.String, cat.Name);
                    db.AddInParameter(dbCommand, "catOrder", DbType.Int32, cat.Order);
                    db.AddInParameter(dbCommand, "catArticleCount", DbType.Int32, cat.ArticleCount);
                    db.AddInParameter(dbCommand, "catHidden", DbType.Boolean, cat.Hidden);
                    db.AddInParameter(dbCommand, "catLocked", DbType.Boolean, cat.Locked);


                    db.ExecuteNonQuery(dbCommand, transaction);
                    dbCommand.Parameters.Clear();
                    dbCommand.CommandText = "SELECT @@IDENTITY";
                    int result = (int)db.ExecuteScalar(dbCommand, transaction);
                    transaction.Commit();
                    return result;
                }
                catch
                {
                    // Roll back the transaction. 
                    transaction.Rollback();
                }
                connection.Close();
            }
            return 0;
        }
        public override void UpdateCategory(Category cat)
        {
            DbCommand dbCommand = db.GetSqlStringCommand("UPDATE `blog_Category` SET `cat_name` = ?, `cat_order` = ?, `cat_articleCount` = ?, `cat_hidden` = ?, `cat_locked` = ? WHERE ((`cat_id` = ?))");
            db.AddInParameter(dbCommand, "catName", DbType.String, cat.Name);
            db.AddInParameter(dbCommand, "catOrder", DbType.Int32, cat.Order);
            db.AddInParameter(dbCommand, "catArticleCount", DbType.Int32, cat.ArticleCount);
            db.AddInParameter(dbCommand, "catHidden", DbType.Boolean, cat.Hidden);
            db.AddInParameter(dbCommand, "catLocked", DbType.Boolean, cat.Locked);
            db.AddInParameter(dbCommand, "catID", DbType.String, cat.CategoryID);
            db.ExecuteNonQuery(dbCommand);
        }
        public override void DeleteCategory(int catID)
        {
            DbCommand dbCommand = db.GetSqlStringCommand("DELETE FROM `blog_Category` WHERE ((`cat_id` = ?))");
            db.AddInParameter(dbCommand, "catID", DbType.Int32, catID);
            db.ExecuteNonQuery(dbCommand);
        }

        public override void MoveCategory(string catIDList, int newCatID)
        {
            string[] catList = catIDList.Split(new char[] { ',' });

            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"
            UPDATE blog_article SET log_catid=? where log_catid in ({0});", catIDList));
            db.AddInParameter(dbCommand, "catIDList", DbType.String, catIDList);
            db.AddInParameter(dbCommand, "newCatID", DbType.Int32, newCatID);
            db.ExecuteNonQuery(dbCommand);

            dbCommand = db.GetSqlStringCommand(string.Format(@"
            UPDATE blog_category SET cat_articleCount=0 where cat_id in ({0});", catIDList));
            db.ExecuteNonQuery(dbCommand);

            dbCommand = db.GetSqlStringCommand(string.Format(@"
            select count(log_id) from blog_article where log_catID={0};", newCatID));
            int count = Convert.ToInt32(db.ExecuteScalar(dbCommand).ToString());
            dbCommand = db.GetSqlStringCommand(string.Format(@"
            UPDATE blog_category SET cat_articleCount={0} where cat_id={1}", count, newCatID));
            db.ExecuteNonQuery(dbCommand);
        }

        #endregion
    }
}
